"""
使用mysqlclient三方库操作MySQL
try - 把可能出现异常状况的代码保护起来执行
except - 捕获异常
else - 没有出现异常的情况下要执行的且不再捕获异常的代码
finally - 总是执行代码（善后工作）
raise - 引发异常抛出异常对象
"""
import MySQLdb as mysql
# import pymysql as mysql


class Dept:

    def __init__(self, no, name, location):
        self.no = no
        self.name = name
        self.location = location

    def __str__(self):
        return f'{self.no}: {self.name}: {self.location}'


# 1. 建立连接
from MySQLdb.cursors import DictCursor

conn = mysql.connect(host='47.104.31.138', port=3306,
                     user='root', password='123456',
                     database='hrs', charset='utf8')
try:
    # 2. 通过连接对象的cursor方法获取游标对象
    # 默认是元组型游标，可以替换为字典型的游标
    with conn.cursor(DictCursor) as cursor:
        # 3. 通过游标对象的execute方法向数据库发出SQL语句
        cursor.execute(
            'select dno as no, dname as name, dloc as location from tb_dept'
        )
        # 4. 通过游标对象的fetchxxx方法获取数据
        #   ~ fetchone / fetchmany / fetchall
        for data_dict in cursor.fetchall():
            dept = Dept(**data_dict)
            print(dept)
except mysql.MySQLError as err:
    print(err)
finally:
    conn.close()
